using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	/// <summary>
	/// Class representing a reservation entry in the database. 
	/// </summary>
	public class Reservation
	{
		private int m_reservation_ID;
		private int m_user_ID;
		private int m_book_ID;
		private string m_bookTitle;
		private string m_book_isbn;
		private string m_reservation_date_str;
		private string m_pickupdate_start_str;
		private string m_pickupdate_end_str;
		private StringBuilder m_user = new StringBuilder("");
		private DateTime m_reservation_date;
		private DateTime m_pickupdate_start;
		private DateTime m_pickupdate_end;
		public Reservation ()
		{
		}
		
		public Reservation (int reservation_ID)
		{
			m_reservation_ID = reservation_ID;
			getData();
		}
		
		public int reservation_ID
		{
			set {
				m_reservation_ID = value;
				getData();
			}
			get {
				return m_reservation_ID;
			}
		}
		
		public int book_ID
		{
			set {
				m_book_ID = value;
			}
			get {
				return m_book_ID;
			}
		}
		
		public int user_ID
		{
			set {
				m_user_ID = value;
			}
			get {
				return m_user_ID;
			}
		}
		
		public DateTime reservation_date
		{
			set {
				m_reservation_date = value;
			}
			get {
				return m_reservation_date;
			}
		}
		
		public DateTime pickupdate_start
		{
			set {
				m_pickupdate_start = value;
			}
			get {
				return m_pickupdate_start;
			}
		}
		
		public DateTime pickupdate_end
		{
			set {
				m_pickupdate_end = value;
			}
			get {
				return m_pickupdate_end;
			}
		}
		
		public string bookTitle
		{
			set {
				m_bookTitle = value;
			}
			get {
				return m_bookTitle;
			}
		}
		
		public string user
		{
			set {
				m_user = new StringBuilder(value);
			}
			get {
				return m_user.ToString();
			}
		}
		
		public string book_isbn
		{
			set {
				m_book_isbn = value;
			}
			get {
				return m_book_isbn;
			}
		}
		
		public string reservation_date_str
		{
			set {
				
			}
			get {
				return m_reservation_date_str;
			}
		}
		
		public string pickupdate_start_str
		{
			set {
				
			}
			get
			{
				return m_pickupdate_start_str;
			}
		}
		
		public string pickupdate_end_str
		{
			set {
				
			}
			get
			{
				return m_pickupdate_end_str;
			}
		}
		
		public static List<Reservation> getReservations()
		{
			List<Reservation> reservationList = new List<Reservation>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_id "
					+ "FROM reservation";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Reservation current = new Reservation((int) reader["reservation_id"]);
					reservationList.Add(current);
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return reservationList;
		}
		
		public static List<Reservation> getReservations(int book_ID)
		{
			List<Reservation> reservationList = new List<Reservation>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_id "
					+ "FROM reservation WHERE book_ID='"+book_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Reservation current = new Reservation((int) reader["reservation_id"]);
					reservationList.Add(current);
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return reservationList;
		}
		
		private void getData()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT * "
					+ "FROM reservation WHERE reservation_id = '" + this.m_reservation_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					this.m_book_ID = (int) reader["book_ID"];
					this.m_user_ID = (int) reader["user_ID"];
					this.m_reservation_date = (DateTime) reader["reservation_date"];
					this.m_pickupdate_start = (DateTime) reader["pickup_date_start"];
					this.pickupdate_end = (DateTime) reader["pickup_date_end"];
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			Book dummy = new Book(this.m_book_ID);
			this.m_book_isbn = dummy.isbn;
			this.m_bookTitle = dummy.title;
			this.m_reservation_date_str = this.m_reservation_date.ToString("t")+this.m_reservation_date.ToString(", MMM d, yyyy");
			if(this.m_pickupdate_start != DateTime.MinValue)
			{
				this.m_pickupdate_start_str = this.m_pickupdate_start.ToString("t")+this.m_pickupdate_start.ToString(", MMM d, yyyy");
			}
			else
			{
				this.m_pickupdate_start_str = "-";
			}
			if(this.m_pickupdate_end != DateTime.MinValue)
			{
				this.m_pickupdate_end_str = this.m_pickupdate_end.ToString("t")+this.m_pickupdate_end.ToString(", MMM d, yyyy");
			}
			else
			{
				this.m_pickupdate_end_str = "-";
			}
			User user = new User(this.m_user_ID);
			this.m_user.Append(user.username+", "+user.firstname+" "+user.lastname);
		}
		
		/// <summary>
		/// Adds a reservation entry in the database. 
		/// </summary>
		/// <param name="book_ID">
		/// A <see cref="System.Int32"/> The book_ID of the book.
		/// </param>
		/// <param name="user_ID">
		/// A <see cref="System.Int32"/> The user_ID of the reserver.
		/// </param>
		/// <param name="reservation_date">
		/// A <see cref="DateTime"/> The date and time that the reserver reserved the book.
		/// </param>
		public static void addReservation(int book_ID, int user_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO reservation (book_ID, user_ID) "
					+ "VALUES ('"+book_ID+"','"+user_ID+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Deletes a reservation entry. 
		/// </summary>
		/// <param name="reservation_ID">
		/// A <see cref="System.Int32"/> The reservation_ID of the reservation to be deleted.
		/// </param>
		public static void deleteReservation(int reservation_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();				
				string statement = "DELETE FROM reservation where reservation_ID='"+reservation_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public static void updateReservationPickupDate(int reservation_ID)
		{
			Reservation reservation = new Reservation(reservation_ID);
			reservation.pickupdate_start = DateTime.Now;
			reservation.pickupdate_end = DateTime.Now.AddDays(7);
			
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();				
				string statement = "UPDATE reservation SET pickup_date_start='"+String.Format("{0:yyyy-MM-dd HH:mm:ss}", reservation.pickupdate_start)+"'," 
					+ " pickup_date_end='"+String.Format("{0:yyyy-MM-dd HH:mm:ss}", reservation.pickupdate_end)+"'"
					+ " where reservation_ID='"+reservation_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
	}
}